//116 tutorial.
//http://www.youtube.com/watch?NR=1&v=WKf5CvuQwAQ
//

package databases;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import bookless.pakage.Book;

public class BookDB {

	public static final String KEY_BOOKID = "_id";
	public static final String KEY_BOOKNAME = "book_name";
	public static final String KEY_AUTHOR = "author";
	public static final String KEY_PRICE = "price";
	public static final String KEY_COMMENT = "comment";

	private static final String DATABASE_NAME = "bookDB";
	private static final String BOOKDB_TABLE = "bookTable";
	private static final int DATABASE_VERSION = 2;

	private DbHelper ourHelper;
	private final Context ourContext;
	private SQLiteDatabase ourDatabase;

	private static class DbHelper extends SQLiteOpenHelper {

		public DbHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		// this method creates our database! this is only called once.
		@Override
		public void onCreate(SQLiteDatabase db) {
			// -------------------------CONTINUE WITH TABLE
			// BELOW--------------------
			// TODO Auto-generated method stub
			// creating the database table. specifying the table name and
			// different variables in the table.
			db.execSQL("CREATE TABLE " + BOOKDB_TABLE + " (" + KEY_BOOKID
					+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_BOOKNAME
					+ " TEXT NOT NULL, " + KEY_AUTHOR + " TEXT NOT NULL, "
					+ KEY_PRICE + " TEXT NOT NULL, " + KEY_COMMENT
					+ " TEXT NOT NULL);");
		}

		// if oncreate() is called, onupgrade() is called instead.
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// TODO Auto-generated method stub
			db.execSQL("DROP TABLE IF EXISTS " + BOOKDB_TABLE);
			onCreate(db);

		}
	}

	public BookDB(Context c) {
		ourContext = c;
	}

	public BookDB open() throws SQLException {
		ourHelper = new DbHelper(ourContext);
		ourDatabase = ourHelper.getWritableDatabase();
		return this;
	}

	// closes SQLiteOpenHelper
	public void close() {
		ourHelper.close();
	}

	public long createEntry(String title, String author, String price,
			String comment) {
		// TODO Auto-generated method stub
		ContentValues cv = new ContentValues();
		cv.put(KEY_BOOKNAME, title);
		cv.put(KEY_AUTHOR, author);
		cv.put(KEY_PRICE, price);
		cv.put(KEY_COMMENT, comment);
		// insert data into database
		return ourDatabase.insert(BOOKDB_TABLE, null, cv);
	}

	public String getData() {
		// TODO Auto-generated method stub
		String[] columns = new String[] { KEY_BOOKID, KEY_BOOKNAME, KEY_AUTHOR,
				KEY_PRICE, KEY_COMMENT };

		Cursor bcursor = ourDatabase.query(BOOKDB_TABLE, columns, null, null,
				null, null, null); // possible to enter other info in null

		String result = "";

		int iRow = bcursor.getColumnIndex(KEY_BOOKID);
		int iTitle = bcursor.getColumnIndex(KEY_BOOKNAME);
		int iAuthor = bcursor.getColumnIndex(KEY_AUTHOR);
		int iPrice = bcursor.getColumnIndex(KEY_PRICE);
		int iComment = bcursor.getColumnIndex(KEY_COMMENT);

		// loop that iterates through the db list.
		for (bcursor.moveToFirst(); !bcursor.isAfterLast(); bcursor
				.moveToNext()) {
			result = result + bcursor.getString(iRow) + " Title: "
					+ bcursor.getString(iTitle) + " Author: "
					+ bcursor.getString(iAuthor) + " Price: "
					+ bcursor.getString(iPrice) + " Comments: "
					+ bcursor.getString(iComment) + "\n";

		}
		return result;

	}

	public ArrayList<Book> getBooksByAuthor(String a) {

		Cursor cursor = ourDatabase.query(BOOKDB_TABLE, new String[] {
				KEY_BOOKNAME, KEY_AUTHOR, KEY_PRICE, KEY_COMMENT }, KEY_AUTHOR
				+ "=\'" + a + "\'", null, null, null, null);

		ArrayList<Book> list = new ArrayList<Book>();

		int iTitle = cursor.getColumnIndex(KEY_BOOKNAME);
		int iAuthor = cursor.getColumnIndex(KEY_AUTHOR);
		int iPrice = cursor.getColumnIndex(KEY_PRICE);
		int iComment = cursor.getColumnIndex(KEY_COMMENT);

		// loop that iterates through the db list.
		for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
			list.add(new Book(cursor.getString(iTitle), cursor
					.getString(iAuthor), cursor.getString(iPrice), cursor
					.getString(iComment)));

		}

		return list;
	}

	public ArrayList<Book> getBooksByTitle(String t) {

		Cursor cursor = ourDatabase.query(BOOKDB_TABLE, new String[] {
				KEY_BOOKNAME, KEY_AUTHOR, KEY_PRICE, KEY_COMMENT },
				KEY_BOOKNAME + "=\'" + t + "\'", null, null, null, null);

		ArrayList<Book> list = new ArrayList<Book>();

		// int iRow = cursor.getColumnIndex(KEY_BOOKID);
		int iTitle = cursor.getColumnIndex(KEY_BOOKNAME);
		int iAuthor = cursor.getColumnIndex(KEY_AUTHOR);
		int iPrice = cursor.getColumnIndex(KEY_PRICE);
		int iComment = cursor.getColumnIndex(KEY_COMMENT);

		// loop that iterates through the db list.
		for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
			list.add(new Book(cursor.getString(iTitle), cursor
					.getString(iAuthor), cursor.getString(iPrice), cursor
					.getString(iComment)));

		}

		return list;
	}

	public ArrayList<Book> getAllBooks() {

		Cursor cursor = ourDatabase.query(BOOKDB_TABLE, new String[] {
				KEY_BOOKNAME, KEY_AUTHOR, KEY_PRICE, KEY_COMMENT }, null, null,
				null, null, null);

		ArrayList<Book> list = new ArrayList<Book>();

		// int iRow = cursor.getColumnIndex(KEY_BOOKID);
		int iTitle = cursor.getColumnIndex(KEY_BOOKNAME);
		int iAuthor = cursor.getColumnIndex(KEY_AUTHOR);
		int iPrice = cursor.getColumnIndex(KEY_PRICE);
		int iComment = cursor.getColumnIndex(KEY_COMMENT);

		// loop that iterates through the db list.
		for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
			list.add(new Book(cursor.getString(iTitle), cursor
					.getString(iAuthor), cursor.getString(iPrice), cursor
					.getString(iComment)));

		}

		return list;
	}

	public ArrayList<Long> deleteBook(Book b) {

		String title = b.getTitle();
		String author = b.getAuthor();
		String price = b.getPrice();
		String comment = b.getComments();

		String[] columns = new String[] { KEY_BOOKID, KEY_BOOKNAME, KEY_AUTHOR,
				KEY_PRICE, KEY_COMMENT };
		
		Cursor c = ourDatabase.query(BOOKDB_TABLE, columns, null, null, null,
				null, null);
		
		
		int iRow = c.getColumnIndex(KEY_BOOKID);
		int iTitle = c.getColumnIndex(KEY_BOOKNAME);
		int iAuthor = c.getColumnIndex(KEY_AUTHOR);
		int iPrice = c.getColumnIndex(KEY_PRICE);
		int iComment = c.getColumnIndex(KEY_COMMENT);

		c.moveToPosition(1);
		c.moveToFirst();
		
		ArrayList<Long> removedIds = new ArrayList<Long>();

		long tmp;

		while (!c.isAfterLast()) {

			if (c.getString(iTitle).equals(title)
					&& c.getString(iAuthor).equals(author)
					&& c.getString(iPrice).equals(price)
					&& c.getString(iComment).equals(comment)
					) {
				tmp = c.getLong(iRow);
				removedIds.add(tmp);
				ourDatabase.delete(BOOKDB_TABLE, KEY_BOOKID + "=" + tmp, null);				
			}
			
			c.moveToNext();
		}
		return removedIds;
	}

	 public Book getBookById(long id) {
	
	 String[] columns = new String[] { KEY_BOOKID, KEY_BOOKNAME, KEY_AUTHOR,
	 KEY_PRICE, KEY_COMMENT };
	
	 Book book = null;
	 Cursor c = ourDatabase.query(BOOKDB_TABLE, columns, null, null, null,
	 null, null);
	
	 int iRow = c.getColumnIndex(KEY_BOOKID);
	 int iTitle = c.getColumnIndex(KEY_BOOKNAME);
	 int iAuthor = c.getColumnIndex(KEY_AUTHOR);
	 int iPrice = c.getColumnIndex(KEY_PRICE);
	 int iComment = c.getColumnIndex(KEY_COMMENT);
	
	 c.moveToPosition(0);
	 c.moveToFirst();
	
	 while (!c.isAfterLast()) {
	 long tmp = c.getLong(iRow);
	
	 if (tmp == id) {
	 book = new Book(c.getString(iTitle), c.getString(iAuthor),
	 c.getString(iPrice), c.getString(iComment));
	 }
	 c.moveToNext();
	 }
	 return book;
	 }
	 
	 public void deleteBookById(long id){
		 
			ourDatabase.delete(BOOKDB_TABLE, KEY_BOOKID + "=" + id, null);
		 
	 }

	public void removeAllBooks() {
        
		ourDatabase.delete(BOOKDB_TABLE, null, null);

		
	}
}
